package com.ph.irp.stock.dao.impl;

import com.ph.util.DBUtil;
import com.ph.util.DateUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PStockInStorageDetail;
import com.ph.irp.stock.dao.StockInStorageDetailDAO;

public class StockInStorageDetailDAOImpl implements StockInStorageDetailDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1777437481880702996L;
	private ComboPooledDataSource dataSource;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addStockInStorageDetail(
			PStockInStorageDetail stockInStorageDetail) throws Throwable {

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb
				.append("insert into p_stock_stockinstoragedetail(StockInStorageID, MaterialID, StorageID, ");
		sb
				.append("MeasureUnitId, BasicUnitShouldAmount, ShouldAmount, BasicUnitFactAmount, FactAmount, ");
		sb
				.append("Money, unitPrice, Remark, InstorageDate, InStoreLife, ValidationDate, SourceBillCode, ");
		sb
				.append("SourceOrderCode, TicketAmount, BaiscTicketAmount, FetchOutAmount, materialNO, SuperStockCode) ");
		sb.append("values(");
		sb.append(stockInStorageDetail.getStockInStorageId());
		sb.append(", ");
		sb.append(stockInStorageDetail.getMaterialId());
		sb.append(", ");
		sb.append(stockInStorageDetail.getStorageId());
		sb.append(", ");
		sb.append(stockInStorageDetail.getMeasureUnitId());
		sb.append(", ");
		sb.append(stockInStorageDetail.getBasicUnitShouldAmount());
		sb.append(", ");
		sb.append(stockInStorageDetail.getShouldAmount());
		sb.append(", ");
		sb.append(stockInStorageDetail.getBasicUnitFactAmount());
		sb.append(", ");
		sb.append(stockInStorageDetail.getFactAmount());
		sb.append(", ");
		sb.append(stockInStorageDetail.getMoney());
		sb.append(", ");
		sb.append(stockInStorageDetail.getUnitPrice());
		sb.append(", ");
		if (stockInStorageDetail.getRemark() == null
				|| stockInStorageDetail.getRemark().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getRemark() + "'");
		sb.append(", ");
		if (stockInStorageDetail.getInStorageDate() == null
				|| stockInStorageDetail.getInStorageDate().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getInStorageDate() + "'");
		sb.append(", ");
		if (stockInStorageDetail.getInStoreLife() == 0)
			sb.append("null");
		else
			sb.append(stockInStorageDetail.getInStoreLife());
		sb.append(", ");
		if (stockInStorageDetail.getValidationDate() == null
				|| stockInStorageDetail.getValidationDate().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getValidationDate() + "'");
		sb.append(", ");
		if (stockInStorageDetail.getSourceBillCode() == null
				|| stockInStorageDetail.getSourceBillCode().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getSourceBillCode() + "'");
		sb.append(", ");
		if (stockInStorageDetail.getSourceOrderCode() == null
				|| stockInStorageDetail.getSourceOrderCode().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getSourceOrderCode() + "'");
		sb.append(", ");
		if (stockInStorageDetail.getTicketAmount() == 0)
			sb.append("null");
		else
			sb.append(stockInStorageDetail.getTicketAmount());
		sb.append(", ");
		if (stockInStorageDetail.getBaiscTicketAmount() == 0)
			sb.append("null");
		else
			sb.append(stockInStorageDetail.getBaiscTicketAmount());
		sb.append(", ");
		if (stockInStorageDetail.getFetchOutAmout() == 0)
			sb.append("null");
		else
			sb.append(stockInStorageDetail.getFetchOutAmout());
		sb.append(", ");
		if (stockInStorageDetail.getMaterialNo() == null
				|| stockInStorageDetail.getMaterialNo().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getMaterialNo() + "'");
		sb.append(", ");
		if (stockInStorageDetail.getSuperStockCode() == null
				|| stockInStorageDetail.getSuperStockCode().trim().equals(""))
			sb.append("null");
		else
			sb.append("'" + stockInStorageDetail.getSuperStockCode() + "'");
		sb.append(");");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	@Override
	public void deleteStockInStorageDetail(String stockInStorageDetailId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb
				.append("delete from p_stock_stockinstoragedetail where StockInStorageDetailID = ?");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, Integer.parseInt(stockInStorageDetailId));
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void deleteStockInStorageDetailByInStorageId(String stockInStorageId)
			throws Throwable {
		
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("delete from p_stock_stockinstoragedetail where StockInStorageID = ?");
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, Integer.parseInt(stockInStorageId));
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
			DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		
	}

	@Override
	public List<PStockInStorageDetail> getStockInStorageDetailsByStockInStorageId(
			int stockInStorageId) throws Throwable {

		List<PStockInStorageDetail> stockInStorageDetails = null;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_stock_stockinstoragedetail where StockInStorageID = ?;");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, stockInStorageId);
			rs = pStmt.executeQuery();
			stockInStorageDetails = turnRSToList(rs);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return stockInStorageDetails;
	}

	@Override
	public PStockInStorageDetail getStockInStorageDetailById(
			int stockInStorageDetailId) throws Throwable {
		PStockInStorageDetail stockInStorageDetail = new PStockInStorageDetail();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_stock_stockinstoragedetail where StockInstorageDetailID = ?;");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.setInt(1, stockInStorageDetailId);
			rs = pStmt.executeQuery();
			List<PStockInStorageDetail> stockInStorageDetails = turnRSToList(rs);
			stockInStorageDetail = stockInStorageDetails.get(0);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return stockInStorageDetail;
	}

	public List<PStockInStorageDetail> turnRSToList(ResultSet rs)
			throws Throwable {
		List<PStockInStorageDetail> list = new ArrayList<PStockInStorageDetail>();

		while (rs.next()) {
			PStockInStorageDetail stockInStorageDetail = new PStockInStorageDetail();
			stockInStorageDetail.setStockInStorageDetailId(rs
					.getInt("StockInStorageDetailID"));
			stockInStorageDetail.setStockInStorageId(rs
					.getInt("StockInStorageID"));
			stockInStorageDetail.setMaterialId(rs.getInt("MaterialID"));
			stockInStorageDetail.setStorageId(rs.getInt("StorageID"));
			stockInStorageDetail.setMeasureUnitId(rs.getInt("MeasureUnitId"));
			stockInStorageDetail.setBasicUnitShouldAmount(rs
					.getFloat("BasicUnitShouldAmount"));
			stockInStorageDetail.setShouldAmount(rs.getFloat("ShouldAmount"));
			stockInStorageDetail.setBasicUnitFactAmount(rs
					.getFloat("BasicUnitFactAmount"));
			stockInStorageDetail.setFactAmount(rs.getFloat("FactAmount"));
			stockInStorageDetail.setMoney(rs.getFloat("Money"));
			stockInStorageDetail.setUnitPrice(rs.getFloat("unitPrice"));
			stockInStorageDetail.setRemark(rs.getString("Remark"));
			Date ISDate = rs.getDate("InStorageDate");
			if (ISDate == null)
				stockInStorageDetail.setInStorageDate(null);
			else
				stockInStorageDetail.setInStorageDate(DateUtil.date2String(
						ISDate, DateUtil.YMD));
			stockInStorageDetail.setInStoreLife(rs.getFloat("InStoreLife"));
			Date VDate = rs.getDate("ValidationDate");
			if (VDate == null)
				stockInStorageDetail.setValidationDate(null);
			else
				stockInStorageDetail.setValidationDate(DateUtil.date2String(
						VDate, DateUtil.YMD));
			stockInStorageDetail.setSourceBillCode(rs
					.getString("SourceBillCode"));
			stockInStorageDetail.setSourceOrderCode("SourceOrderCode");
			stockInStorageDetail.setTicketAmount(rs.getFloat("TicketAmount"));
			stockInStorageDetail.setBaiscTicketAmount(rs
					.getFloat("BaiscTicketAmount"));
			stockInStorageDetail
					.setFetchOutAmout(rs.getFloat("FetchOutAmount"));
			stockInStorageDetail.setMaterialNo(rs.getString("materialNO"));
			stockInStorageDetail.setSuperStockCode(rs
					.getString("SuperStockCode"));

			list.add(stockInStorageDetail);

		}

		return list;
	}

}
